<?php

/**
 * This file is part of the Codizer package.
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 *
 * @copyright   Copyright (c) 2010 Advisa (http://www.advisa.fr)
 * @author      Codizer Core Team <team@codizer.org>
 * @package     Codizer
 */

class f_builder_DatabaseBuilder
{
	/**
	 * @var f_xml_Xpath
	 */
	private $xmlObject;
	
	private $attributes;
	private $tableName;
	private $extendedTableName;
	private $revisionTableName;
	private $i18nTableName;
	private $i18nRevisionTableName;
	private $model;
	private $properties;
	private $templatesDir;
	private $isRevisable;
	private $i18nProperties;
	private $inWebapp = false;
	private $extends;
    private $confirm = true;
	
	/**
	 * @return f_builder_DatabaseBuilder
	 */
	private function __construct()
	{		
		return $this;
	}
	
	/**
	 * Get instance
	 *
	 * @return f_builder_DatabaseBuilder
	 */
	public static function getInstance()
	{
		return new self();	
	}	

    public function noConfirm()
    {
        $this->confirm = false;
        return $this;
    }

	/**
	 * @param f_xml_Xpath $xmlObject
	 * @return f_builder_DatabaseBuilder
	 */
	public function setXmlObject(f_xml_Xpath $xmlObject)
	{
		if(is_object($xmlObject) && ($xmlObject instanceof f_xml_Xpath))
		{
			$this->xmlObject = $xmlObject;
			$this->setVariables();
		}
		return $this;
	}
	
	/**
	 * Sets class variables
	 *
	 * @return f_builder_DatabaseBuilder
	 */
	private function setVariables()
	{
		$this->templatesDir = FRAMEWORK_HOME.DS."builder".DS."templates".DS;
		
		if(is_object($this->xmlObject))
		{
			$attributes = $this->xmlObject->fromXpath("document")->getAttributes();
			
			// Name
			if(!isset($attributes["name"]) || is_null($attributes["name"]) || $attributes["name"] == "")
			{
				$tmp = explode(DS,$this->xmlObject->getXmlFilePath());
				$attributes["name"] = str_replace(".xml","",end($tmp));
			}
			
			// Module
			if(!isset($attributes["module"]) || is_null($attributes["module"]) || $attributes["module"] == "")
			{
				$attributes["module"] = $this->getModuleName();
			}
			
			$prefix = "m_";		
			if($this->inWebapp == true)
			{
				$prefix = "w_";
			}		
			
			$moduleName = $attributes['module'];
			$documentName = $attributes['name'];
			
			$this->tableName = $prefix.$moduleName."__".$documentName;
			
			// Extends class
			$this->extends = "f_document_BaseDocument";
			if(isset($attributes["extends"]) && !is_null($attributes["extends"]) && $attributes["extends"] != "")
			{
				$this->extends = $attributes["extends"];				
			}
			
			// In case of extended documents
			if($this->extends != "f_document_BaseDocument")
			{
				$this->extendedTableName = $this->getFinalTableName($this->extends,$this->xmlObject,$prefix);
			}
			
			$this->revisionTableName = $this->tableName."_revisions";
			$this->i18nTableName = $this->tableName."_i18n";
			$this->i18nRevisionTableName = $this->tableName."_revisions_i18n";
			
			$this->model = "modules_".$moduleName."/".$documentName;
			
			$properties = $this->xmlObject->getElementsFromXpath("properties/add");
			
			$this->attributes = $attributes;
			$this->properties = $properties;
			$this->i18nProperties = $this->getI18nProperties();
			$this->isRevisable = $this->getRevisionProperty();

			$this->getDefaultPublicationStatus();
		}
		return $this;
	}
	
	/**
	 * Create initial tables in database
	 */
	public function createInitialDatabaseTables()
	{
		$templateEngine = f_template_Template::getInstance();
		
		$tables = array("f_document", "f_relation", "f_target", "f_packages");
		
		$this->templatesDir = FRAMEWORK_HOME.DS."builder".DS."templates".DS;
		
		foreach($tables as $tableName)
		{
			$templateFile = $this->templatesDir."SQL_".$tableName.".tpl";

			$sql = $templateEngine->setTemplate($templateFile)->disableCache()->render();
		
			if(!$this->checkIfTableExists($tableName))
			{
				$dbProvider = f_database_Provider::getInstance()->setQuery($sql)->execute();
				if(CLI_MODE && $this->confirm) echo COLOR_GREEN."Writing".COLOR_NORMAL." -> create table ".$tableName." in database\n";
			}
			else
			{
				if(CLI_MODE && $this->confirm) echo COLOR_RED."Skipped".COLOR_NORMAL." -> Table ".$tableName." already exists in database, remove if necessary\n";
			}
		}
	}	

	/**
	 * Create the sql file(s) and table(s) in database for the given xml document
	 */
	public function createDocumentTable()
	{
		$this->writeBaseTable();
		
		if($this->isRevisable == "true")
		{
			$this->writeRevisionsTable();
		}
		
		if($this->isI18nFields())
		{
			$this->writeI18nTables();
		}
	}
		
	/**
	 * Set if files must be saved in webapp or modules dir
	 *
	 * @param boolean $boolean
	 */
	public function isInWebapp($boolean = false)
	{
		$this->inWebapp = $boolean;
		return $this;
	}
	
	/**
	 * Write base table for a document
	 */
	private function writeBaseTable()
	{
		$fileName = $this->tableName.".sql";
	    if($this->extends == "f_document_BaseDocument")
		{
			$this->writeTable($fileName, $this->tableName);
		}
		else
		{
			$this->writeTable($fileName, $this->extendedTableName);
		}
		
	}
	
	/**
	 * Write revision table for a document
	 */	
	private function writeRevisionsTable()
	{
		$fileName = $this->tableName."_revisions.sql";
	    if($this->extends == "f_document_BaseDocument")
		{
			$this->writeTable($fileName, $this->revisionTableName);
		}
		else
		{
			$this->writeTable($fileName, $this->extendedTableName."_revisions");
		}		
	}
	
	/**
	 * generic method to write in database and in a sql file
	 *
	 * @param string $fileName
	 * @param string $tableName
	 */
	private function writeTable($fileName, $tableName)
	{
		$docAttributes = $this->attributes;
		
		$check = $this->checkIfTableExists($tableName);
		
		// If table doesn't exist
		if(isset($this->xmlObject) && !$check)
		{
			$templateEngine = f_template_Template::getInstance();
			
			$templateFile = $this->templatesDir."SQL_GenericDocumentTable.tpl";
			
			$templateEngine->setTemplate($templateFile)->disableCache();
			
			$templateEngine->assign("tableName", $tableName);
			$templateEngine->assign("defaultStatus", $this->getDefaultPublicationStatus());
			$templateEngine->assign("fields", $this->getVars($this->properties));
			
			$content = $templateEngine->render();		

			if(CLI_MODE && $this->confirm) echo COLOR_GREEN."Writing".COLOR_NORMAL." -> create table ".$tableName." in database\n";
			
			$res = f_database_Provider::getInstance()->setQuery($content)->execute();

			
			if($res)
			{
				$path = DS.$docAttributes['module'].DS."sql";
							
				$this->writeFile($path, $fileName, $content);
			}
		}
		else if(isset($this->xmlObject) && $check)
		{

			if(CLI_MODE && $this->confirm) echo COLOR_RED."Skipped".COLOR_NORMAL." -> Table ".$tableName." already exists in database, remove if necessary\n";

			// now check new fields not in db
				
			$dbProvider = f_database_Provider::getInstance();
			$fields = $dbProvider->setQuery("DESCRIBE $tableName")->execute()->getQueryResults();
			
			foreach($fields as $field)
			{
				$oldFields[] = $field['Field'];
			}
						
			$newFields = array();

			foreach($this->properties as $key => $var)
			{
				$attributes = $var->getAttributes();
				if(!in_array($attributes['name'], $oldFields))
				{
					$newFields[] = $attributes;

					if(CLI_MODE && $this->confirm) echo COLOR_BEIGE."Info".COLOR_NORMAL."    -> Field `".$attributes['name']."` doesn't exist in `".$tableName."` table\n";
				}
			}

			$patch = true;
			if($this->confirm && count($newFields) > 0)
			{
				if(!$this->askQuestion("Add field in table ".$tableName." ?"))
				{
					$patch = false;
				}
			}
			
			// Patching the db
			if($patch == true && count($newFields) > 0)
			{
				$templateEngine = f_template_Template::getInstance();
				
				$templateFile = $this->templatesDir."SQL_AlterTable.tpl";
				
				$templateEngine->setTemplate($templateFile)->disableCache();
				
				$templateEngine->assign("tableName", $tableName);
				$templateEngine->assign("fields", $this->getVars($newFields,true));
				
				$content = $templateEngine->render();	
				
				if(CLI_MODE && $this->confirm) echo COLOR_GREEN."Writing".COLOR_NORMAL." -> alter table ".$tableName." in database\n";
				
				$res = f_database_Provider::getInstance()->setQuery($content)->execute();
				
				if($res)
				{
					$path = DS.$docAttributes['module'].DS."sql";
							
					$this->writeFile($path, $fileName, $content);
				}
			}
		}
	}
	
	/**
	 * Write i18n tables
	 */
	private function writeI18nTables()
	{		
		if($this->extends == "f_document_BaseDocument")
		{
			$this->writeI18nTable($this->i18nTableName);
		}
		else
		{
			$this->writeI18nTable($this->extendedTableName."_i18n");
		}				
		
		if($this->isRevisable == "true")
		{
			$fileName = $this->tableName."_revisions_i18n.sql";
		    if($this->extends == "f_document_BaseDocument")
			{
				$this->writeTable($fileName, $this->i18nRevisionTableName);	
			}
			else
			{
				$this->writeTable($fileName, $this->extendedTableName."_revisions_i18n");
			}					
		}		
	}
	
	/**
	 * Write i18n table in database
	 */	
	private function writeI18nTable($tableName)
	{
		$attributes = $this->attributes;
		
		$check = $this->checkIfTableExists($tableName);
		
		$fileName = $tableName.".sql";	
		
		if(!$check)
		{
			$templateEngine = f_template_Template::getInstance()->disableCache();
			
			$templateFile = $this->templatesDir."SQL_GenericI18nTable.tpl";
			
			$templateEngine->setTemplate($templateFile);
			
			$templateEngine->assign("tableName", $tableName);
			$templateEngine->assign("originalTableName", str_replace('_i18n', '', $tableName));
			$templateEngine->assign("fields", $this->getSQLFields());
			
			$content = $templateEngine->render();		
			
			if(CLI_MODE && $this->confirm) echo COLOR_GREEN."Writing".COLOR_NORMAL." -> create table ".$tableName." in database\n";
			
			$res = f_database_Provider::getInstance()->setQuery($content)->execute();
		
			if($res)
			{
				$path = DS.$attributes['module'].DS."sql";
				
				$this->writeFile($path, $fileName, $content);
			}				
		}
		else
		{
			if(CLI_MODE && $this->confirm) echo COLOR_RED."Skipped".COLOR_NORMAL." -> Table `".$tableName."` already exists in database, remove if necessary\n";
			
			// now check new fields not in db
			
			$dbProvider = f_database_Provider::getInstance();
			$fields = $dbProvider->setQuery("DESCRIBE $tableName")->execute()->getQueryResults();
			
			foreach($fields as $field)
			{
				$oldFields[] = $field['Field'];
			}
		
			$newFields = array();
			$xmlFields = $this->i18nProperties;
			
			foreach($xmlFields as $key => $var)
			{
				$attributes = $this->getFieldProperties($var->getAttributes());

				if(!is_null($attributes['name']) && !in_array($attributes['name'], $oldFields))
				{
					$newFields[] = $attributes;
					if(CLI_MODE && $this->confirm) echo COLOR_BEIGE."Info".COLOR_NORMAL."    -> Field `".$attributes['name']."` doesn't exist in `".$tableName."` table\n";
				}
			}

			$patch = true;
			if($this->confirm && count($newFields) > 0)
			{
				if(!$this->askQuestion("Add field in table ".$tableName." ?"))
				{
					$patch = false;
				}
			}
			
			// Patching the db
			if($patch == true && count($newFields) > 0)
			{
				$templateEngine = f_template_Template::getInstance();
				
				$templateFile = $this->templatesDir."SQL_AlterTable.tpl";
				
				$templateEngine->setTemplate($templateFile)->disableCache();
				
				$templateEngine->assign("tableName", $tableName);
				$templateEngine->assign("fields", $this->getVars($newFields,true));
				
				$content = $templateEngine->render();	
				
				if(CLI_MODE && $this->confirm) echo COLOR_GREEN."Writing".COLOR_NORMAL." -> alter table ".$tableName." in database\n";
				
				$res = f_database_Provider::getInstance()->setQuery($content)->execute();
				
				if($res)
				{
					$path = DS.$this->attributes['module'].DS."sql";
								
					$this->writeFile($path, $fileName, $content);
				}
			}			
		}
	}

    /**
	 * Get the Table name of the final parent
	 *
	 * @param string $extends
	 * @param object $xmlObject
	 * @param string $prefix
	 * @return string tableName
	 */
	private function getFinalTableName($extends,$xmlObject,$prefix)
	{
		// Case of extended document
		if($extends != "f_document_BaseDocument")
		{
			// Split extends value to get the dir, module and document values
			$tmp = explode("_",$extends);
			
			if(count($tmp) != 4)
			{
				throw new Exception("bad extends value");
			}
						
			$tmpPrefix = strtolower($tmp[0]);
			
			if($tmpPrefix == "m")
			{
				$dir = MODULES_DIR;
			}
			else
			{
				$dir = WEBAPP_MODULES_DIR;
			}
			
			$module = strtolower($tmp[1]);
			$document = strtolower($tmp[3]);
			
			// Xml file of the extended document
			$file = $dir.DS.$module.DS."documents".DS.$document.".xml";

			// If file exist
			if(is_file($file))
			{
				// Read it
				$xmlFileObject = f_xml_Xpath::getInstance($file);
				$attributes = $xmlFileObject->fromXpath("document")->getAttributes();
				
				// Get the extends value
				$tmpExtends = "f_document_BaseDocument";
				if(isset($attributes["extends"]) && !is_null($attributes["extends"]) && $attributes["extends"] != "")
				{
					$tmpExtends = $attributes["extends"];				
				}
				
				// Return his tableName
				return $this->getFinalTableName($tmpExtends,$xmlFileObject,$prefix);
			}
			else
			{
				throw new Exception("File $file doesn't exist");
			}			
		}
		else
		{	
			// Case of final parent docuement
			
			// Read the xml file and return the formated table name
			$attributes = $xmlObject->fromXpath("document")->getAttributes();
			return $prefix.$attributes['module']."_doc_".$attributes['name'];
		}
	}
	
	/**
	 * Retrieve module name from the xml file path
	 * 
	 * @return string
	 */
	private function getModuleName()
	{
		$fileDetails = f_file_File::getInstance($this->xmlObject->getXmlFilePath());
				
		$filename = $fileDetails->getFileName();
		$dirname = $fileDetails->getDir();
		
		if(strstr($dirname, MODULES_DIR))
		{
			$dirname = str_replace(MODULES_DIR.DS, "", $dirname);
		}
		else
		{
			$dirname = str_replace(WEBAPP_MODULES_DIR.DS, "", $dirname);
		}
		
		$vars = explode(DS, $dirname);
		
		return $vars[0];		
	}	
		
	/**
	 * Get content from <localization> if exists
	 *
	 * @return f_xml_Element;
	 */
	private function getI18nProperties()
	{
		$fields = $this->xmlObject->getElementsFromXpath("localization/field");
		if(isset($fields[0]))
		{
			return $fields;
		}
		return null;
	}
	
	/**
	 * Check if there are i18nFields
	 *
	 * @return boolean
	 */
	private function isI18nFields()
	{
		if(count($this->i18nProperties) >= 1)
		{
			return true;
		}
		return false;
	}
		
	/**
	 * return attribues of the localized $field
	 *
	 * @param array $field
	 * @return array
	 */
	private function getFieldProperties($field)
	{
		foreach($this->properties as $key => $var)
		{	
			$attributes = $var->getAttributes();
			if($attributes['name'] == $field['name'])
			{
				return $attributes;
			}
			else
			{
			
			}
		}
	}
	
	/**
	 * Write sql properties for the current field 
	 *
	 * @param array $fieldArray
	 * @return string
	 */
	private function formatFieldToSql($fieldArray)
	{
		$fieldName = $fieldArray['Field'];
		$fieldType = $fieldArray['Type'];
		$fieldIsNull = $fieldArray['Null'];
		$fieldDef = $fieldArray['Default'];
		
		is_null($fieldDef) ? $fieldDefault = "" : $fieldDefault = "default '$fieldDef'";
		$fieldIsNull == "YES" ? $fieldNull = "NULL" : $fieldNull = "";
		
		$sql = sprintf("	`%s` %s %s %s, ", $fieldName, $fieldType, $fieldNull, $fieldDefault);
		
		return $sql;
	}
	
	/**
	 * Get fields to localize with attributes
	 *
	 * @return array
	 */
	private function getFieldsToLocalize()
	{
		// Get fields to localize from xml file
		$xmlFields = $this->i18nProperties;

		$tableFields = array();
		if(isset($xmlFields[0]))
		{
			/* @var $xmlField f_xml_XmlElement */
			foreach($xmlFields as $xmlField)
			{
				$tableFields[] = $xmlField->getAttributeValue("name");
			}
		}
		
		// Get base table fields to localize
		$dbProvider = f_database_Provider::getInstance();
		$fields = $dbProvider->setQuery("DESCRIBE ".$this->tableName)->execute()->getQueryResults();
		
		$fieldsToLocalise = array();
		
		foreach ($fields as $field)
		{
			if(in_array($field['Field'], $tableFields))
			{
				$fieldsToLocalise[] = $field;
			}
		}
		
		return $fieldsToLocalise;		
	}
	
	/**
	 * Get all additionnal fields
	 *
	 * @return string
	 */
	private function getSQLFields()
	{
		$fields = $this->getFieldsToLocalize();
		
		$sqlFields = array();
		
		$exclude = array('document_uid', 'document_creationdate', 'document_modificationdate', 'document_lang', 'document_revision', 'document_label');
		
		foreach ($fields as $field)
		{
			if(!in_array($field['Field'], $exclude))
			{
				$sqlFields[] = $this->formatFieldToSql($field);	
			}				
		}
		
		$sql = join("\n", $sqlFields);
		
		return $sql;
	}
		
	/**
	 * Check if table defined in xml file already exists
	 *
	 * @return boolean
	 */
	private function checkIfTableExists($tableName)
	{
		return f_database_Provider::getInstance()->tableExists($tableName);
	}
	
	/**
	 * Set the field values
	 * @param array properties  
	 * @param bool $alterTable  ( false for an "create table" action, true for an "alter table" action)
	 * @return string
	 */
	private function getVars($properties, $alterTable = false)
	{
		$content = "";
		
		// In case of "alter table"
		$alterTxt = "";
		if($alterTable == true)
		{
			$alterTxt = "ADD ";
		}
		
		if($properties != NULL)
		{
			$isObject = false;
			if($properties instanceof f_xml_Xpath)
			{
				$isObject = true;
			}
			
			foreach($properties as $key => $var)
			{
				if($isObject == true)
				{
					$attributes =  $var->getAttributes();		
				}
				else
				{
					$attributes =  $var;		
				}

				$content .= "	  ".$alterTxt."`".$attributes['name']."` ";
				
				if(!preg_match("#modules_(.*)/(.*)#", strtolower($attributes['type'])))
				{
					switch (strtolower($attributes['type']))
					{
						case "boolean":
							$content .= "tinyint(1) ";
                            $content .= isset($attributes['default']) ? 'NOT NULL DEFAULT \''.$attributes['default'].'\'': 'NULL';
						break;
						
						case "datetime":
							$content .= "datetime";
						break;
									
						case "integer":
							$content .= "int(11) ";
                            $content .= isset($attributes['default']) ? 'NOT NULL DEFAULT \''.$attributes['default'].'\'': 'NULL';
						break;

			            case "decimal":
				            //Number of digits and digits to the right of decimal point
				            if(!isset($attributes["format"]) || is_null($attributes["format"]) || $attributes["format"] == "")
				            {
				            	$decimalFormat = "12,2";	
				            }
				            else
				            {
				               	$decimalFormat = $attributes["format"];
				            }
			              	$content .= "DECIMAL(" .$decimalFormat. ") ";
                            $content .= isset($attributes['default']) ? 'NOT NULL DEFAULT \''.$attributes['default'].'\'': 'NULL';
			            break;
											
						case "string":
						case "password":						
							$content .= "varchar(255) ";
                            $content .= isset($attributes['default']) ? 'NOT NULL DEFAULT \''.$attributes['default'].'\'': 'NULL';
						break;	
													
						case "xhtml":
							$content .= "text ";
                            $content .= isset($attributes['default']) ? 'NOT NULL DEFAULT \''.$attributes['default'].'\'': 'NULL';
						break;
					}				
				}
				else
				{
					$content .= "int(11) NULL";
				}
	
				$content .= ","."\n";
			}
			
			// In case of "alter table"
			if($alterTable == true)
			{
				// Delete last ","
				$pos = strrpos($content, ",");
				$content = substr($content,0,$pos);
			}
		}
		return $content;
	}	
	
	/**
	 * Get the revision property value from the given xml document
	 *
	 * @return string|null
	 */
	private function getRevisionProperty()
	{
		return $this->xmlObject->fromXpath("revision")->getAttributeValue("isrevisable");
	}
	
	/**
	 * Get the default publication status from the given xml document
	 * 
	 * @return string
	 */
	private function getDefaultPublicationStatus()
	{
		return "default '".strtoupper($this->xmlObject->fromXpath("statuses")->getAttributeValue("default", "DRAFT"))."'";
	}
	
	/**
	 * Write sql file
	 *
	 * @param string $fileName
	 * @param string $fileContent
	 */
	private function writeFile($path, $fileName, $fileContent)
	{
		if($this->inWebapp)
		{
			$moduleSQLpath = WEBAPP_MODULES_DIR.$path;				
		}
		else
		{
			$moduleSQLpath = MODULES_DIR.$path;	
		}
		
		$file = $moduleSQLpath.DS.$fileName;
		
		if(!is_dir($moduleSQLpath))
		{
			if(CLI_MODE) echo COLOR_GREEN."Writing".COLOR_NORMAL." -> create path $moduleSQLpath\n";
			mkdir($moduleSQLpath, 0755, true);
		}	
						
		if(file_exists($file))
		{
			if(CLI_MODE && $this->confirm) echo COLOR_RED."Skipped".COLOR_NORMAL." -> File ".$fileName." already exist, remove if necessary\n";
			return null;
		}

		if(CLI_MODE && $this->confirm) echo COLOR_GREEN."Writing".COLOR_NORMAL." -> ".$file."\n";
		
		$fp = fopen($file, "w");
		fwrite($fp, $fileContent);
		fclose($fp);				
	}

	/**
	 * Print $question in STDOUT and wait for answer
	 * 
	 * @param string $question
	 * @return boolean
	 */
	private function askQuestion($question)
	{
		fwrite(STDOUT, COLOR_GREEN."Confirm".COLOR_NORMAL." -> $question (y/n)\n");
		$test = trim(fgets(STDIN));
		
		if($test != "n" && $test != "y")
		{
			$this->askQuestion($question);
		}
		
		if($test == "y")
		{
			return true;
		}
		
		return false;
	}	
}
